﻿USE Kairos;
GO

--FULLTEXT CATALOG

DECLARE @fulltext_catalog_id int = 0;

IF NOT EXISTS(SELECT 1 FROM sys.fulltext_catalogs WHERE name='Kairos')
	CREATE FULLTEXT CATALOG Kairos WITH ACCENT_SENSITIVITY = OFF

SET @fulltext_catalog_id = (SELECT fulltext_catalog_id FROM sys.fulltext_catalogs WHERE name='Kairos')

--FULLTEXT INDEX ON Graphes

IF NOT EXISTS (SELECT 1 FROM sys.fulltext_index_columns, sys.fulltext_indexes
	LEFT JOIN sys.tables ON sys.fulltext_indexes.object_id = sys.tables.object_id
	LEFT JOIN sys.columns ON sys.columns.object_id = sys.tables.object_id
	WHERE 
		sys.fulltext_indexes.fulltext_catalog_id = @fulltext_catalog_id AND
		sys.fulltext_index_columns.object_id = sys.tables.object_id AND 
		sys.fulltext_index_columns.column_id = sys.columns.column_id AND
		sys.tables.name = 'Graphes' AND
		sys.columns.name = 'Titre')

	CREATE FULLTEXT INDEX ON Graphes
	(
		Titre								--Full-text index column name 
			--TYPE COLUMN FileExtension		--Name of column that contains file type information
			Language 1036					--1036 is the LCID for French
	)
	KEY INDEX [PK_dbo.Graphes] ON Kairos	--Unique index
	WITH CHANGE_TRACKING AUTO				--Population type;
	GO

--FULLTEXT CATALOG

DECLARE @fulltext_catalog_id int = 0;

IF NOT EXISTS(SELECT 1 FROM sys.fulltext_catalogs WHERE name='Kairos')
	CREATE FULLTEXT CATALOG Kairos WITH ACCENT_SENSITIVITY = OFF

SET @fulltext_catalog_id = (SELECT fulltext_catalog_id FROM sys.fulltext_catalogs WHERE name='Kairos')

--FULLTEXT INDEX ON Documents

IF NOT EXISTS (SELECT 1 FROM sys.fulltext_index_columns, sys.fulltext_indexes
	LEFT JOIN sys.tables ON sys.fulltext_indexes.object_id = sys.tables.object_id
	LEFT JOIN sys.columns ON sys.columns.object_id = sys.tables.object_id
	WHERE 
		sys.fulltext_indexes.fulltext_catalog_id = @fulltext_catalog_id AND
		sys.fulltext_index_columns.object_id = sys.tables.object_id AND 
		sys.fulltext_index_columns.column_id = sys.columns.column_id AND
		sys.tables.name = 'Documents' AND
		sys.columns.name IN ('Titre','Contenu'))

	CREATE FULLTEXT INDEX ON Documents
	(
		Titre								--Full-text index column name 
			--TYPE COLUMN FileExtension		--Name of column that contains file type information
			Language 1036,					--1036 is the LCID for French
		Contenu								--Full-text index column name 
			--TYPE COLUMN FileExtension		--Name of column that contains file type information
			Language 1036					--1036 is the LCID for French
	)
	KEY INDEX [PK_dbo.Documents] ON Kairos	--Unique index
	WITH CHANGE_TRACKING AUTO				--Population type;
	GO

--FREETEXT SearchGraphes FUNCTION

IF EXISTS (SELECT 1 FROM sys.objects WHERE name = 'SearchGraphes')
	DROP FUNCTION SearchGraphes
	GO

CREATE FUNCTION SearchGraphes (@searchstring nvarchar(4000)) RETURNS TABLE
	RETURN
	SELECT Graphes.* FROM 
		FREETEXTTABLE ([dbo].[Graphes], [Titre], @searchstring) A
		INNER JOIN [dbo].[Graphes] ON [A].[KEY] = [dbo].[Graphes].[ID]
	GO

--FREETEXT SearchGraphes FUNCTION

IF EXISTS (SELECT 1 FROM sys.objects WHERE name = 'SearchDocuments')
	DROP FUNCTION SearchDocuments
	GO

CREATE FUNCTION SearchDocuments (@searchstring nvarchar(4000)) RETURNS TABLE
	RETURN
	SELECT Documents.* FROM 
		FREETEXTTABLE ([dbo].[Documents], ([Titre],[Contenu]), @searchstring) A
		INNER JOIN [dbo].[Documents] ON [A].[KEY] = [dbo].[Documents].[ID]
	GO